package DBWrapper::DatabaseBuilder;

# $Id: DatabaseBuilder.pm 270 2010-07-18 13:32:40Z dolbanavt $
# $Date: 2010-07-18 16:33:01 +0300 (Вск, 18 Июл 2010) $
# $HeadURL: https://robosearch.googlecode.com/svn/branches/reborn/lib/DBWrapper/DatabaseBuilder.pm $
# $LastChangedRevision: 351 $

use parent qw( DBWrapper );
use JIP::ToolSet;
use Carp qw( croak );
use English qw( -no_match_vars );
use Const::Fast qw( const );
use Host;

our $VERSION = '1.0';

const my $DELETE_FROM_FILES => q{
    DELETE FROM
        files
    WHERE
        files.host_id
    IN
        (
            SELECT
                builds.host_id
            FROM
                (
                    SELECT
                        host_id,
                        MAX( last_build ) AS date_of_last_build
                    FROM
                        database_builds
                    GROUP BY
                        host_id
                ) AS builds
            WHERE
                builds.date_of_last_build < SUBDATE( NOW(), INTERVAL ? HOUR )
        )
};

const my $DELETE_FROM_DIRECTORIES => q{
    DELETE FROM
        directories
    WHERE
        directories.host_id
    IN
        (
            SELECT
                builds.host_id
            FROM
                (
                    SELECT
                        host_id,
                        MAX( last_build ) AS date_of_last_build
                    FROM
                        database_builds
                    GROUP BY
                        host_id
                ) AS builds
            WHERE
                builds.date_of_last_build < SUBDATE( NOW(), INTERVAL ? HOUR )
        )
};

const my $SELECT_HOSTS_FOR_LOAD => q{
    SELECT
        DISTINCT( hosts.id ) AS host_identifier,
        hosts.ip_address     AS host_ip_address
    FROM
        hosts,
        (
            SELECT   host_id, MAX( last_crawl ) AS date_of_last_crawl
            FROM     crawls
            GROUP BY host_id
        ) AS last_crawls,
        (
            SELECT   host_id, MAX( last_build ) AS date_of_last_build
            FROM     database_builds
            GROUP BY host_id
        ) AS last_builds
    WHERE
        hosts.id = last_builds.host_id
        AND
        hosts.id = last_builds.host_id
        AND
        last_crawls.date_of_last_crawl > last_builds.date_of_last_build
        AND
        last_crawls.date_of_last_crawl > SUBDATE( NOW(), INTERVAL ? HOUR )
};

sub remove_files_older_then {
    my ( $self, $ageing ) = @ARG;

    croak 'ageing required' unless defined $ageing;

    $self->get_dbh->do( $DELETE_FROM_FILES, {}, $ageing )
        or croak 'cannot update data: '. $self->get_dbh->errstr;

    $self->get_dbh->do( $DELETE_FROM_DIRECTORIES, {}, $ageing )
        or croak 'cannot update data: '. $self->get_dbh->errstr;

    return 1;
}

sub get_hosts_for_load {
    my ( $self, $ageing ) = @ARG;

    croak 'ageing required' unless defined $ageing;

    my $statement = $self->get_dbh->prepare( $SELECT_HOSTS_FOR_LOAD )
        or croak 'cannot prepare: '. $self->get_dbh->errstr;

    $statement->execute( $ageing )
        or croak 'cannot execute: '. $self->get_dbh->errstr;

    my @hosts;

    while ( my $ln = $statement->fetchrow_hashref() ) {
        push @hosts, Host->new(
            host_id    => $ln->{'host_identifier'},
            ip_address => $ln->{'host_ip_address'},
        );
    }

    return @hosts;
}

sub remove_files_for {
    my ( $self, $host_id ) = @ARG;

    croak 'host_id required' unless defined $host_id;

    $self->get_dbh->do(
        q{
            DELETE
                f.*, d.*
            FROM
                files AS f INNER JOIN directories AS d
                ON f.host_id = d.host_id
            WHERE
                f.host_id = ?
        },
        {},
        $host_id,
    ) or croak 'cannot update data: '. $self->get_dbh->errstr;

    return 1;
}

sub update_status_for {
    my ( $self, $host_id ) = @ARG;

    croak 'host_id required' unless defined $host_id;

    $self->get_dbh->do(
        q{
            INSERT INTO database_builds ( host_id, last_build )
            VALUES ( ?, NOW() )
        },
        {},
        $host_id
    ) or croak 'cannot insert data: '. $self->get_dbh->errstr;

    return 1;
}

sub add_new_file {
    my ( $self, $item ) = @ARG;

    croak 'item required' unless defined $item;

    $self->get_dbh->do(
        q{
            INSERT INTO files ( host_id, name, path, size, content )
            VALUES ( ?, ?, ?, ?, ? )
        },
        {},
        $item->{'host_id'},
        $item->{'name'},
        $item->{'path'},
        $item->{'size'},
        $item->{'content'},
    ) or croak 'cannot insert data: '. $self->get_dbh->errstr;

    return 1;
}

sub add_new_dir {
    my ( $self, $item ) = @ARG;

    croak 'item required' unless defined $item;

    $self->get_dbh->do(
        q{
            INSERT INTO directories ( host_id, name, path )
            VALUES ( ?, ?, ? )
        },
        {},
        $item->{'host_id'},
        $item->{'name'},
        $item->{'path'},
    ) or croak 'cannot insert data: '. $self->get_dbh->errstr;

    return 1;
}

1;

__END__

=head1 NAME

DBWrapper::DatabaseBuilder - обертка над базой данных для билдера БД

=head1 SYNOPSIS

    use DBWrapper::DatabaseBuilder;

    my $t = DBWrapper::DatabaseBuilder->new( dbh => DBI->new() );

=head1 DEPENDENCIES

Const::Fast (from CPAN)

JIP::ToolSet (from base_lib)

=head1 AUTHOR

Vladimir Zhavoronkov, <dolbanavt (at) Google Mail>

http://dolbanavt.livejournal.com/

=head1 LICENSE AND COPYRIGHT

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

=cut
